package com.ideal.hadoopadmin.crontab.db;

import com.ideal.hadoopadmin.crontab.property.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by CC on 2015/7/7.
 */
public class ConnectionManager {

    private static Logger logger = LoggerFactory.getLogger(ConnectionManager.class);
    /**
     * 获取webapp 数据库的连接
     * @return
     */
    public static Connection getConnection(){
        String ip= Properties.instance().getPropertyByKey(Properties.WEB_APP_DB_IP,"10.5.24.151");
        String db=Properties.instance().getPropertyByKey(Properties.WEB_APP_DB_DB, "hadoopadminweb_db");
        String user=Properties.instance().getPropertyByKey(Properties.WEB_APP_DB_USER,"hadoopadmin");
        String pw= Properties.instance().getPropertyByKey(Properties.WEB_APP_DB_PW,"ShHadwb910");
        Connection conn=null;
        try {
            /**这里先写死  以后放到配置文件中去*/
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://"+ip+":3306/"+db+"?user="+user+"&password="+pw;
            conn = DriverManager.getConnection(url);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

    /**
     * 获取remote db数据库的连接
     * @return
     */
    public static Connection getRemoteSynConnection(){
        String ip=Properties.instance().getPropertyByKey(Properties.REMOTE_SYN_IP,"10.5.24.151");
        String db=Properties.instance().getPropertyByKey(Properties.REMOTE_SYN_DB, "hadoopadminweb_db");
        String user=Properties.instance().getPropertyByKey(Properties.REMOTE_SYN_USER,"hadoopadmin");
        String pw= Properties.instance().getPropertyByKey(Properties.REMOTE_SYN_PW,"ShHadwb910");
        Connection conn=null;
        try {
            /**这里先写死  以后放到配置文件中去*/
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://"+ip+":3306/"+db+"?user="+user+"&password="+pw;
            logger.info("url:"+url);
            conn = DriverManager.getConnection(url);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

    /**
     * 获取webapp 数据库的连接
     * @return
     */
    public static Connection getHiveConnection(){
        String ip=Properties.instance().getPropertyByKey(Properties.HIVE_META_IP,"10.5.24.151");
        String db=Properties.instance().getPropertyByKey(Properties.HIVE_META_DB, "hadoopadminweb_db");
        String user=Properties.instance().getPropertyByKey(Properties.HIVE_META_USER,"hadoopadmin");
        String pw= Properties.instance().getPropertyByKey(Properties.HIVE_META_PW,"ShHadwb910");
        Connection conn=null;
        try {
            /**这里先写死  以后放到配置文件中去*/
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://"+ip+":3306/"+db+"?user="+user+"&password="+pw;
            logger.info("url:"+url);
            conn = DriverManager.getConnection(url);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

    public static Connection getSentryConnection(){
        String ip=Properties.instance().getPropertyByKey(Properties.SENTRY_META_IP,"10.5.24.148");
        String db=Properties.instance().getPropertyByKey(Properties.SENTRY_META_DB, "sentry");
        String user=Properties.instance().getPropertyByKey(Properties.SENTRY_META_USER,"sentry");
        String pw= Properties.instance().getPropertyByKey(Properties.SENTRY_META_PW,"sentry123");
        Connection conn=null;
        try {
            /**这里先写死  以后放到配置文件中去*/
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://"+ip+":3306/"+db+"?user="+user+"&password="+pw;
            conn = DriverManager.getConnection(url);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

    public static Connection getConnection(String host,String db,String user,String passwd){
        Connection conn=null;
        try {
            /**这里先写死  以后放到配置文件中去*/
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://"+host+":3306/"+db+"?user="+user+"&password="+passwd+"";
            logger.info("url:"+url);
            conn = DriverManager.getConnection(url);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

    public static void main(String[] args){
        Properties properties= Properties.instance();
        String ip=properties.getPropertyByKey(Properties.WEB_APP_DB_IP,"");
        logger.info("ip "+ip);

    }

    public static List<Map<String,Object>> queryDB(Connection conn,String sql){
        List<Map<String,Object>> rsList=new ArrayList<Map<String, Object>>();

        Statement stmt=null;
        ResultSet rs= null;
        logger.info("sql:"+sql);
        try {

            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            //resultset
            ResultSetMetaData rsMeta=rs.getMetaData();
            int colNumbers=rsMeta.getColumnCount();
            String[] cols=new String[colNumbers];
            for(int i=1;i<=colNumbers;i++){
                String colName=rsMeta.getColumnName(i);
                cols[i-1]=colName;
            }

            while(rs.next()){
                Map<String,Object> colMap=new HashMap<String, Object>();
                for(int i=1;i<=colNumbers;i++){
                    //放入列名和值
                    colMap.put(cols[i-1].toUpperCase(),rs.getObject(i));

                }
                rsList.add(colMap);
            }

        } catch (SQLException e) {
//            e.printStackTrace();
        } finally {
            try {
                if(rs!=null){
                    rs.close();
                }
                if(stmt!=null){
                    stmt.close();
                }
                closeConn(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return rsList;
    }


    /***
     * 批量执行 插入语句
     * @param sqlList
     */
    public static void exeSQLBatch(List<String> sqlList){
        Connection conn=ConnectionManager.getConnection();
        Statement stmt = null;
        try {
            conn.setAutoCommit(false);

            stmt = conn.createStatement();
            for(String sql:sqlList) {
                stmt.addBatch(sql);
            }
            stmt.executeBatch();

            //commit
            conn.commit();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            ConnectionManager.closeConn(conn);
        }
    }

    /***
     * 批量执行 插入语句
     * @param sql
     */
    public static void exeSQL(String sql){
        Connection conn=ConnectionManager.getConnection();
        Statement stmt = null;
        try {

            stmt = conn.createStatement();
            stmt.execute(sql);

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            ConnectionManager.closeConn(conn);
        }
    }

    public static void closeConn(Connection conn){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
